Acala

Row

Overall Total Value Locked (TVL)

  • The Overall TVL in the table below does not includes the Crowdloan TVL, which also seems to be the case on the stats.Acala.network page.
Date Block dexTVL M maxDate bridgeTVL liquidStakingTVL crowdloanTVL LPTokenStakingTVL lcdotTVL stableCoinTVL OverallTVL
2022-02-28 496,214 19,375,345 22,022 19,051 3,758,400,000 0 611,040,301 21,454,201 453,190,874 35,851,414 4,288,271,834
2022-03-31 711,104 23,395,737 32,022 19,082 42,972,000 0 698,638,351 23,720,373 518,159,808 96,421,301 704,669,220
2022-04-30 921,375 20,113,100 42,022 19,112 29,036,000 0 472,067,001 19,512,552 350,118,407 70,866,527 489,646,586
2022-05-31 1,134,939 11,805,351 52,022 19,143 74,274,661 58,963,767 340,474,829 11,948,136 252,520,309 96,860,090 506,372,314
2022-06-28 1,321,430 8,597,192 62,022 19,171 58,370,922 46,384,270 247,218,998 8,598,317 183,355,163 68,192,884 373,498,748

Row

Components of Total Value Locked

Row

Sources

Please click on the Source Code link at the top of the document to see the full source code. Here is a summary:

dexTVL

  • sum of tvlUSD from the xx function in the subscanr package for R. You can also use the Karura-dex project hosted on Subquery Network with this query:
{ 
  dailyPools { 
    nodes { 
      timestamp token0 {id} token1 {id} feeRateUSD dailyTradeVolumeUSD totalTVL txCount updateAtBlock {id}  
    } 
  } 
}

LPTokenStakingTVL

  • ACA (or KAR) obtained from the py-substrate-interface using function substrate.query(module=‘Rewards’,storage_function=‘PoolInfos’, block_hash = hash)
  • mulitplied by ACA (or KAR) price for the specific block number

BridgeTVL

  • KSM (or DOT) obtained from the py-substrate-interface using function substrate.query(module=‘Tokens’,storage_function=‘TotalIssuance’, params = [{‘Token’: ‘KSM’}], block_hash = hash)
  • LKSM (or LDOT) obtained from function substrate.query(module=‘Tokens’,storage_function=‘TotalIssuance’, params = [{‘Token’: ‘LKSM’}], block_hash = hash)
  • both mulitplied by KSM (or DOT) price for the specific block number

liquidStakingTVL

  • KSM (or DOT) obtained from the py-substrate-interface using function substrate.query(module=‘Homa’,storage_function=‘TotalStakingBonded’, block_hash = hash)
  • mulitplied by KSM (or DOT) price for the specific block number

lcdotTVL (Acala only)

  • LCDOT obtained from the py-substrate-interface using function substrate.query(module=‘Tokens’,storage_function=‘TotalIssuance’, params = [{‘LiquidCrowdloan’: 13}], block_hash = hash)
  • mulitplied by DOT price for the specific block number

stableCoinTVL

{ 
  dailyCollaterals { 
    nodes { 
      collateral {id} depositAmount debitAmount depositVolumeUSD debitVolumeUSD
      depositChangedUSD debitChangedUSD debitExchangeRate timestamp txCount 
    } 
  } 
}
---
title: "Acala / Karura TVL Dashboard"
output:
  flexdashboard::flex_dashboard:
    orientation: rows
    vertical_layout: scroll
    social: menu
    source_code: embed
params:
  network: Karura
  window: 120
  
---

```{css custom1, echo=FALSE}
.dataTables_scrollBody {
    max-height: 100% !important;
}
```

```{r global, include=FALSE}
library(knitr)
knitr::opts_chunk$set(
  message = FALSE,
  warning = FALSE,
  comment = "#>"
)

library(ggplot2)
# library(dygraphs)
library(kableExtra)
library(formattable)
library(lubridate)
library(flexdashboard)
library(DT)
library(subscanr)
library(formattable)
library(ghql)
x <- GraphqlClient$new()

# Helper function to concat
`%+%` <- function(a, b) paste0(a, b)

# window = params$window
window <- today() - as.Date("2021-12-31") + 1
network = params$network

# Liquidity Pool TVL ($3.72M) = totalDefiTvl = totalDexTvl + totalDexLockedTvl + homaLocked + stableCoinTvl
dex <- getDailyPools_acala_dex(network, window) %>%
  setorder(Date, pair)

dailyTVL <- dex[, .(max(updateAtBlock.id), sum(tvlUSD)), by = Date] %>%
  setnames(c("V1","V2"), c("Block","dexTVL"))

library(reticulate)
# use_python("/opt/homebrew/bin/python3.9")
```

```{python, include=FALSE}
from substrateinterface import SubstrateInterface

import pandas as pd

def getTotalIssuanceKSM():
    url = 'wss://karura.polkawallet.io'
    substrate = SubstrateInterface(url)
    hash = substrate.get_block_hash(block_id)
    timestamp = substrate.query(module='Timestamp',storage_function='Now',block_hash=hash).value
  
    # Issuance
    ksm = substrate.query(module='Tokens',storage_function='TotalIssuance', params = [{'Token': 'KSM'}], block_hash = hash)
    lksm = substrate.query(module='Tokens',storage_function='TotalIssuance', params = [{'Token': 'LKSM'}], block_hash = hash)
    result = substrate.query('AcalaOracle', 'Values', params = [{'Token': 'KSM'}], block_hash = hash)
  
    # homa = substrate.query(module='Homa',storage_function='TotalStakingBonded', block_hash = hash)
    try:
        homa = substrate.query_map(module='Homa',storage_function='StakingLedgers', block_hash = hash)
        amount = 0
        for res in homa:
            amount += res[1].value['bonded']
            
        # LKSM to KSM ratio
        ratio = lksm.value / amount 

    except:
        amount = 0
        # LKSM to KSM ratio
        ratio = 8.5
    
    # Build price DataFrame
    p = substrate.query('AcalaOracle', 'Values', params = [{'Token': 'KSM'}], block_hash = hash)
    price = p.value['value'] / 10**18
  
    bridge = (float(ksm.value / 10**12) + float(lksm.value / 10**12 / ratio)) * price
    liquidKSM = float(amount / 10**12) * price
    crowdloanTVL = (501137661910050505 / 10**12) * price
    return [bridge, liquidKSM, crowdloanTVL, block_id, ratio]

def getOraclePrices():
    if r.network=="Karura":
        url = 'wss://karura.polkawallet.io'
    else:
        url = 'wss://acala-rpc-0.aca-api.network'
      
    substrate = SubstrateInterface(url)
    hash = substrate.get_block_hash(block_id)
    timestamp = substrate.query(module='Timestamp',storage_function='Now',block_hash=hash).value
    p = substrate.query_map('AcalaOracle', 'Values', block_hash = hash)
    data = []
    for res in p:
        outi = {"token": str(res[0].value), "price": res[1].value['value'] / 10**18}
        data.append(outi)
    
    out = pd.DataFrame(data)
    return out
 
 
def getRewards():
    if r.network=="Karura":
        url = 'wss://karura.polkawallet.io'
    else:
        url = 'wss://acala-rpc-0.aca-api.network'
      
    substrate = SubstrateInterface(url)
    hash = substrate.get_block_hash(block_id)
    timestamp = substrate.query(module='Timestamp',storage_function='Now',block_hash=hash).value
  
    data = []
    rewards = substrate.query_map(module='Rewards',storage_function='PoolInfos', block_hash = hash)
    for res in rewards:
        if res[0][0] == 'Dex':
            pair = res[0].value['Dex']['DexShare']
            lp = substrate.query(module='Tokens',storage_function='TotalIssuance', params = [{'DexShare': pair}], block_hash = hash)
            legs = substrate.query(module='Dex',storage_function='LiquidityPool', params = [pair], block_hash = hash)
            outi = {"pair0": str(pair[0]), "pair1": str(pair[1]), "lp": str(lp.value), "leg0": str(legs[0].value), "leg1": str(legs[1].value)}
            data.append(outi)
    
    out = pd.DataFrame(data)
    return out

def getTotalIssuanceDOT():
    url = 'wss://acala-rpc-0.aca-api.network'
    substrate = SubstrateInterface(url)
    hash = substrate.get_block_hash(block_id)
    timestamp = substrate.query(module='Timestamp',storage_function='Now',block_hash=hash).value
  
    # Total Issuance data
    dot = substrate.query(module='Tokens',storage_function='TotalIssuance', params = [{'Token': 'DOT'}], block_hash = hash)
    ldot = substrate.query(module='Tokens',storage_function='TotalIssuance', params = [{'Token': 'LDOT'}], block_hash = hash)
    lcdot = substrate.query(module='Tokens',storage_function='TotalIssuance', params = [{'LiquidCrowdloan': 13}], block_hash = hash)
    
    # homa = substrate.query(module='Homa',storage_function='TotalStakingBonded', block_hash = hash)
    try:
        homa = substrate.query_map(module='Homa',storage_function='StakingLedgers', block_hash = hash)
        amount = 0
        for res in homa:
            amount += res[1].value['bonded']
            
        # LKSM to KSM ratio
        ratio = ldot.value / amount 

    except:
        amount = 0
        # LDOT to DOT ratio
        ratio = 10
    
    # Build price DataFrame
    tmp = []
    p = substrate.query_map('AcalaOracle', 'Values', block_hash = hash)
    outi = {"id": {'Token': 'AUSD'}, "price": 1}
    tmp.append(outi)
    for res in p:
      outi = {"id": res[0].value, "price": res[1].value['value'] / 10**18}
      tmp.append(outi)
      if res[0].value=={'Token': 'DOT'}:
        outi = {"id": {'LiquidCrowdloan': 13}, "price": res[1].value['value'] / 10**18 / 1.4}
        tmp.append(outi)
        outi = {"id": {'Token': 'LDOT'}, "price": res[1].value['value'] / 10**18 / ratio}
        tmp.append(outi)
  
    # Price lookup function
    p = substrate.query('AcalaOracle', 'Values', params = [{'Token': 'DOT'}], block_hash = hash)
    price = p.value['value'] / 10**18
    
    # Decimals lookup function
    tmp = [{'id': {'Token': 'ACA'}, 'decimals': 12}, {'id': {'Token': 'DOT'}, 'decimals': 10}, {'id': {'Token': 'LDOT'}, 'decimals': 10}, {'id': {'LiquidCrowdloan': 13}, 'decimals': 10}, {'id': {'Token': 'AUSD'}, 'decimals': 12}]
    decimals = pd.DataFrame(tmp)
    def getDecimals(token):
      return int(decimals[decimals['id'] == token]['decimals'])        
  
    bridge = (float(dot.value / 10**10) + float(ldot.value / 10**10 / ratio)) * price
    liquidDOT = float(amount / 10**10) * price
    lcDOT = float(lcdot.value / 10**10) * price
    crowdloanTVL = (325159802323576263 / 10**10) * price
    return [bridge, liquidDOT, crowdloanTVL, lcDOT, block_id, ratio]

```



```{r tvl, cache = TRUE, include=FALSE}

dailyTVL[, M := month(Date) %+% year(Date)]
dailyTVL[, maxDate := max(Date), by = M]
dailyTVL <- dailyTVL[Date == maxDate]

# LP Token Staking TVL = totalDexTvl = useTotalDexPoolTVL()
# totalDexLockedTvl = useTotalStaking() = api.query.dex?.liquidityPool
# KSM Bridge TVL = totalIssuanceKSM = useTotalIssuance('KSM') = api.query.tokens?.totalIssuance
dailyTVL[, bridgeTVL := 0]
# Liquid KSM TVL = totalLocked in Homa = useState()
dailyTVL[, liquidStakingTVL := 0]
# homeLocked = totalStaking * price
dailyTVL[, crowdloanTVL := 0]
dailyTVL[, LPTokenStakingTVL := 0]
if (tolower(network) == "acala") dailyTVL[, lcdotTVL := 0]

for (i in 1:nrow(dailyTVL)) {
  py_run_string("block_id = " %+% dailyTVL$Block[i])
  if (tolower(network) == "karura") {
    tmp = py$getTotalIssuanceKSM()
  } else {
    tmp = py$getTotalIssuanceDOT()
    dailyTVL$lcdotTVL[i] <- tmp[4]
  }
  dailyTVL$bridgeTVL[i] <- tmp[1]
  dailyTVL$liquidStakingTVL[i] <- tmp[2]
  dailyTVL$crowdloanTVL[i] <- tmp[3]
  
  # LPTokenStakingTVL 
  rewards1 <- py$getRewards()
  rewards <- as.data.table(rewards1)
  # sort(unique(c(rewards$pair0, rewards$pair1)))
  rewards[, pair0 := fixToken(pair0)]
  rewards[, pair1 := fixToken(pair1)]
  rewards <- merge(rewards, tokens, by.x = 'pair0', by.y="Token")
  setnames(rewards, "decimals", "decimals0")
  rewards[, Name := NULL]
  rewards <- merge(rewards, tokens, by.x = 'pair1', by.y="Token")
  setnames(rewards, "decimals", "decimals1")
  rewards[, Name := NULL]
  rewards[, decimals0 := as.numeric(decimals0)]
  rewards[, decimals1 := as.numeric(decimals1)]
  rewards[, lpDecimals := 12]
  rewards[pair0=='DOT' & pair1=='LCDOT', lpDecimals := 10]
  rewards[, lp := as.numeric(lp) / 10**lpDecimals]
  rewards[, leg0 := as.numeric(leg0) / 10**decimals0]
  rewards[, leg1 := as.numeric(leg1) / 10**decimals1]
  
  price = py$getOraclePrices() %>%
    as.data.table
  price[, token := fixToken(token)]
  
  rewards <- merge(rewards, price, by.x = "pair0", by.y="token", all.x = TRUE)
  setnames(rewards, "price", "price0")
  rewards <- merge(rewards, price, by.x = "pair1", by.y="token", all.x = TRUE)
  setnames(rewards, "price", "price1")
  rewards[pair0 %in% c('KUSD', "AUSD"), price0 := 1]
  rewards[pair1 %in% c('KUSD', "AUSD"), price1 := 1]
  rewards[, tvl := leg0 * price0 + leg1 * price1]
  rewards[is.na(tvl), tvl := (leg0 * price0) * 2]
  rewards[is.na(tvl), tvl := (leg1 * price1) * 2]
  rewards[, lpPrice := tvl / lp]  
  dailyTVL$LPTokenStakingTVL[i] <- sum(rewards$tvl, na.rm = TRUE)

}



# stableCoinTvl
# const stableCoinTVL = useTotalLocked(); = api.query.loans?.totalPositions
collateral <- getLoansDailyCollateral_acala_loan(network, window, staging = FALSE) %>%
  setorder(Date, collateral.id)
stableCoinTvl <- collateral[, sum(depositVolumeUSD), by = Date] %>%
  setnames("V1", "stableCoinTVL")
dailyTVL <- merge(dailyTVL, stableCoinTvl, by = "Date", all.x = TRUE)

# If monthly, remove these fields
# try(dailyTVL[, M := NULL])
# try(dailyTVL[, maxDate := NULL])

# Make columns numeric
for (col in names(dailyTVL)[-1]) set(dailyTVL, j=col, value=as.numeric(dailyTVL[[col]])) 
if (tolower(network) == "acala") {
  dailyTVL[, OverallTVL := dexTVL + LPTokenStakingTVL + bridgeTVL + liquidStakingTVL + lcdotTVL + stableCoinTVL]
} else {
  dailyTVL[, OverallTVL := dexTVL + LPTokenStakingTVL + bridgeTVL + liquidStakingTVL + stableCoinTVL]
}

```

# `r network` {.tabset}

Row
----

### Overall Total Value Locked (TVL)

* The Overall TVL in the table below does not includes the Crowdloan TVL, which also seems to be the case on the stats.`r network`.network page.

```{r k_tvl}

knitr::kable(dailyTVL, escape = FALSE, format.args = list(big.mark = ",")) %>%
  kable_styling()

```

Row
----

### Components of Total Value Locked

```{r k_plot1}

if (tolower(network) == "acala") {
  dat <- melt(data = dailyTVL[, .(Date, dexTVL, LPTokenStakingTVL, bridgeTVL, liquidStakingTVL, lcdotTVL, stableCoinTVL)], 
            id.vars = "Date",
            value.name = "TVL")
  cols <- c('dexTVL', 'LPTokenStakingTVL', 'bridgeTVL', 'liquidStakingTVL', 'LCDOTTVL', 'stableCoinTVL')
} else {
  dat <- melt(data = dailyTVL[, .(Date, dexTVL, LPTokenStakingTVL, bridgeTVL, liquidStakingTVL, stableCoinTVL)], 
            id.vars = "Date",
            value.name = "TVL")
  cols <- c('dexTVL', 'LPTokenStakingTVL', 'bridgeTVL', 'liquidStakingTVL', 'stableCoinTVL')
  
}
dat[, TVL := TVL / 1e6]
 
# Stacked
ggplot(dat, aes(fill=variable, y=TVL, x=Date)) + 
    geom_bar(position="stack", stat="identity") +
    ggtitle(label="Components of Total Value Locked") + 
    ylab("Totval Value Locked (in millions USD)")

```


Row
----

### Sources

Please click on the *Source Code* link at the top of the document to see the full source code.  Here is a summary:

#### dexTVL 

- sum of tvlUSD from the xx function in the [subscanr](https://github.com/rogerjbos/subscanr) package for R.  You can also use the [Karura-dex](https://explorer.subquery.network/subquery/AcalaNetwork/karura-dex) project hosted on Subquery Network with this query:
```
{ 
  dailyPools { 
    nodes { 
      timestamp token0 {id} token1 {id} feeRateUSD dailyTradeVolumeUSD totalTVL txCount updateAtBlock {id}  
    } 
  } 
}
```

#### LPTokenStakingTVL
- ACA (or KAR) obtained from the [py-substrate-interface](https://github.com/polkascan/py-substrate-interface) using function *substrate.query(module='Rewards',storage_function='PoolInfos', block_hash = hash)*
- mulitplied by ACA (or KAR) price for the specific block number

#### BridgeTVL
- KSM (or DOT) obtained from the [py-substrate-interface](https://github.com/polkascan/py-substrate-interface) using function *substrate.query(module='Tokens',storage_function='TotalIssuance', params = [{'Token': 'KSM'}], block_hash = hash)*
- LKSM (or LDOT) obtained from function *substrate.query(module='Tokens',storage_function='TotalIssuance', params = [{'Token': 'LKSM'}], block_hash = hash)*
- both mulitplied by KSM (or DOT) price for the specific block number

#### liquidStakingTVL
- KSM (or DOT) obtained from the [py-substrate-interface](https://github.com/polkascan/py-substrate-interface) using function *substrate.query(module='Homa',storage_function='TotalStakingBonded', block_hash = hash)*
- mulitplied by KSM (or DOT) price for the specific block number

#### lcdotTVL (Acala only)
- LCDOT obtained from the [py-substrate-interface](https://github.com/polkascan/py-substrate-interface) using function *substrate.query(module='Tokens',storage_function='TotalIssuance', params = [{'LiquidCrowdloan': 13}], block_hash = hash)*
- mulitplied by DOT price for the specific block number

#### stableCoinTVL
- sum of depositVolumeUSD from the *getLoansDailyCollateral_acala_loan* function in the [subscanr](https://github.com/rogerjbos/subscanr) package for R.  You can also use the [Karura-loan]https://explorer.subquery.network/subquery/AcalaNetwork/karura-loan) project hosted on Subquery Network with this query:
```
{ 
  dailyCollaterals { 
    nodes { 
      collateral {id} depositAmount debitAmount depositVolumeUSD debitVolumeUSD
      depositChangedUSD debitChangedUSD debitExchangeRate timestamp txCount 
    } 
  } 
}
```